﻿<cfsilent>
	<cfsetting requesttimeout="300" />
	<cfflush interval="1024" />
	<cfscript>
		
		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		securityAdvice = getProperty("serviceFactory").getBean("securityAdvice");
		markAdvice = getProperty("serviceFactory").getBean("markAdvice");
		sessionAdvice = getProperty("serviceFactory").getBean("sessionAdvice");
		
		sequenceStudentMark = getProperty("serviceFactory").getBean("sequenceStudentMark");
		sequenceStudentMarkAduit = getProperty("serviceFactory").getBean("sequenceStudentMarkAduit");

		temFile = GetTempDirectory() & createUUID() & ".xls";

		markUpdate = 0;
		markInsert = 0;
		
		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		
		currentTS = datetimeAdvice.getCurrentDatetimeString();
		currentIP = securityAdvice.getIP();
		
		tchId = sessionAdvice.getAutherUserID();
		
	</cfscript>
</cfsilent>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>导入正考成绩数据</title>
<link href="<cfoutput>#getProperty('cdnServer')#</cfoutput><cfoutput>#getContextRoot()#</cfoutput>/acadmicRes/assets/css/impoter.css" rel="stylesheet" type="text/css">
</head>

<body>

	<cfif isDefined("FORM.upload")>
		
		<!--- 将文件保存至临时目录 --->
		<cftry>
			<cffile action="upload" fileField="upload" destination="#temFile#" nameconflict="overwrite">
			<cfcatch type="application">
				<p class="error">上传的文件类型不正确, 请使用电子表格作为数据导入文件.</p>
			</cfcatch>
		</cftry>
		
		<!--- 将文件转换为电子表格对象 --->
		<cfif fileExists(temFile)>
			<cftry>
				<cfspreadsheet action="read" src="#temFile#" excludeHeaderRow="true" headerrow="1" query="rs" sheetname="学生成绩">
				<cfcatch type="application">
					<p class="error">电子表格文件格式不正确, 请使用下载的模板文件录入学籍信息</p>
				</cfcatch>
			</cftry>
			
			<!--- 文件已成功转换为查询对象 --->
			<cfif isDefined("rs") and isQuery(rs)>

				<p class="success">尝试读取电子表格数据 ... 完成</p>
				
				<!--- 检查必要数据字段 --->
				<cfset columnChecked = true />
				<cfif not listFindNoCase(rs.columnList, "学年")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "学期")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "学号")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "姓名")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "课程")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "课程号")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "课程性质")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "成绩")><cfset columnChecked = false /></cfif>
				
				<cfif not columnChecked>
					<p class="error">电子表格数据列不完整, 请检查模板文件是否包含以下必须字段信息: 学年,学期,学号,姓名,课程,课程号,课程性质,成绩</p>
				</cfif>
				
				<cfif columnChecked>

					<p class="success">正在检查数据子项 ... 完成</p> 
					
					<cftransaction>
						
                        <cfset sql = "SELECT tch_name FROM t_teacher WHERE tch_id = :teacherId" />
                        
                        <cfset queryObj = new Query( datasource=application.dnsMaster ) />
                        <cfset queryObj.addParam( name="teacherId", value=tchId, cfsqltype="cf_sql_varchar" ) />
                        
                        <cfset rs_teacherEntity = queryObj.execute( sql=sql ).getResult() />
						
						<cfloop query="rs">

							<!--- 检查学期名称 --->
							<cfif listFind( "春,秋,暑期,1,2,3", rs["学期"][rs.currentRow], "," ) >
									
									<!--- 检查学年学期是否正确 --->
                                    <cfset sql = "SELECT term_id, academic_year, academic_term FROM t_term WHERE academic_year = :academicYear AND academic_term = :academicTerm " />
                        
									<cfset queryObj = new Query( datasource=application.dnsMaster ) />
                                    <cfset queryObj.addParam( name="academicYear", value=rs["学年"][rs.currentRow], cfsqltype="cf_sql_char" ) />
                                    <cfset queryObj.addParam( name="academicTerm", value=replace( replace( replace( rs["学期"][rs.currentRow], "春", "2" ), "秋", "1" ), "暑期", "3" ), cfsqltype="cf_sql_char" ) />
                                    
                                    <cfset rs_term = queryObj.execute( sql=sql ).getResult() />
                                    
									
									<cfif rs_term.recordCount eq 1>
																				
											<!--- 检查学号和姓名是否匹配 --->
                                            <cfset sql = "SELECT stu_id FROM t_student WHERE stu_id = :studentId AND stu_name = :studentName " />
                                
                                            <cfset queryObj = new Query( datasource=application.dnsMaster ) />
                                            <cfset queryObj.addParam( name="studentId", value=rs["学号"][rs.currentRow], cfsqltype="cf_sql_varchar" ) />
                                            <cfset queryObj.addParam( name="studentName", value=rs["姓名"][rs.currentRow], cfsqltype="cf_sql_varchar" ) />
                                            
                                            <cfset rs_student = queryObj.execute( sql=sql ).getResult() />
                                            
																				
											<cfif rs_student.recordCount eq 1>

													<!--- 检查课程和课程号是否匹配 --->
                                                    <cfset sql = "SELECT cid, mark_mode, course_name, course_credit FROM t_course WHERE course_code = :courseCode AND course_name = :courseName " />
                                
													<cfset queryObj = new Query( datasource=application.dnsMaster ) />
                                                    <cfset queryObj.addParam( name="courseCode", value=rs["课程号"][rs.currentRow], cfsqltype="cf_sql_varchar" ) />
                                                    <cfset queryObj.addParam( name="courseName", value=rs["课程"][rs.currentRow], cfsqltype="cf_sql_varchar" ) />
                                                    
                                                    <cfset rs_course = queryObj.execute( sql=sql ).getResult() />
                                                    
												
													<cfif rs_course.recordCount eq 1>
															
															<!--- 检查课程性质 --->
                                                            <cfset sql = "SELECT prop_id FROM t_course_prop WHERE prop_name = :propName " />
                                
															<cfset queryObj = new Query( datasource=application.dnsMaster ) />
                                                            <cfset queryObj.addParam( name="propName", value=rs["课程性质"][rs.currentRow], cfsqltype="cf_sql_varchar" ) />
                                                            
                                                            <cfset rs_courseProp = queryObj.execute( sql=sql ).getResult() />
                                                    
													
															<cfif rs_courseProp.recordCount eq 1>
																	
																	<cfif isNumeric( rs["成绩"][rs.currentRow] ) and rs["成绩"][rs.currentRow] gte 0 and rs["成绩"][rs.currentRow] lte 100>
																			
																			<!--- 关键数据检查完成 --->
																			
																			<cfset mark = rs["成绩"][rs.currentRow] />
																			
																			<!--- 
																				$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
																				可选属性 
																				$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
																			--->
																	
																			<!--- 处理可选信息 备注 开课类别 重修 --->
																	
																			<!--- 默认值 --->
																			<cfset markNote = "1" />
																			<cfset taskType = "P" />
																			<cfset retaking = "0" />
																	
																			<!--- 备注 --->
																			<cfif listFindNoCase(rs.columnList, "备注") and len(rs["备注"][rs.currentRow]) >
																				
																				<cfif rs["备注"][rs.currentRow] eq "缺考">
																					<cfset markNote = "2" />
																					<cfset mark = 0 />
																				</cfif>
																				<cfif rs["备注"][rs.currentRow] eq "违纪">
																					<cfset markNote = "9" />
																					<cfset mark = 0 />
																				</cfif>
																				<cfif rs["备注"][rs.currentRow] eq "缓考">
																					<cfset markNote = "3" />
																					<cfset mark = -1 />
																				</cfif>
																				
																			</cfif>
																			
																			<!--- 开课类别 --->
																			<cfif listFindNoCase(rs.columnList, "开课类别") and len(rs["开课类别"][rs.currentRow]) >
																				
																				<cfif rs["开课类别"][rs.currentRow] eq "全校通选课">
																					<cfset taskType = "C" />
																				</cfif>
																				<cfif rs["开课类别"][rs.currentRow] eq "单开班">
																					<cfset taskType = "R" />
																				</cfif>
																				
																			</cfif>
																			
																			<!--- 重修 --->
																			<cfif listFindNoCase(rs.columnList, "重修") and rs["重修"][rs.currentRow] eq "是" >
																				<cfset retaking = "1" />
																			</cfif>
																			
																			
																			<!--- /* 根据成绩计分模式调整分数 */ --->
																			
																			<!--- 成绩规范化 --->
																			<cfswitch expression="#rs_course.mark_mode#">
																				<!--- /* 百分制最多保留2位小数 */ --->
																				<cfcase value="0">
																					<cfset mark = round( mark * 10 ) / 10 />
																				</cfcase>
																				<!--- /* 二级制则转换 90 | 0 */ --->
																				<cfcase value="2">
																					<cfif mark gte 0 and mark lt 60>
																						<cfset mark = 0 />
																					</cfif>
																					<cfif mark gte 60>
																						<cfset mark =90 />
																					</cfif>
																				</cfcase>
																				<!--- /* 四级制则转换 90 | 80 | 70 | 0 */ --->
																				<cfcase value="4">
																					<cfif mark gte 0 and mark lt 60>
																						<cfset mark = 0 />
																					</cfif>
																					<cfif mark gte 60 and mark lt 70>
																						<cfset mark = 70 />
																					</cfif>
																					<cfif mark gte 70 and mark lt 80>
																						<cfset mark = 80 />
																					</cfif>
																					<cfif mark gte 80 >
																						<cfset mark = 90 />
																					</cfif>
																				</cfcase>
																			</cfswitch>
																			
																			<!--- /* 第一步 生成成绩变更审计记录 */ --->
                                                                            <cfset logId = sequenceStudentMarkAduit.getNextID() />
                                                                            
                                                                            <cfset sql = "INSERT INTO t_mark_aduit(
																									opid, tch_id, 
																									stu_id, cid, 
																									op_type, op_ts, 
																									op_ip, operation
																								)VALUES(
																									:operId, :teacherId, 
																									:studentId, :courseId, 
																									:operation, :timeStamp, 
																									:ip, :description
																								)" />
                                                                            
                                                                            <cfset queryObj = new Query( datasource=application.dnsMaster ) />
                                                                            <cfset queryObj.addParam( name="operId", value=logId, cfsqltype="cf_sql_char" ) />
                                                                            <cfset queryObj.addParam( name="teacherId", value=tchId, cfsqltype="cf_sql_varchar" ) />
                                                                            <cfset queryObj.addParam( name="studentId", value=rs_student.stu_id, cfsqltype="cf_sql_varchar" ) />
                                                                            <cfset queryObj.addParam( name="courseId", value=rs_course.cid, cfsqltype="cf_sql_varchar" ) />
                                                                            <cfset queryObj.addParam( name="operation", value="IMP", cfsqltype="cf_sql_char" ) />
                                                                            <cfset queryObj.addParam( name="timeStamp", value=currentTS, cfsqltype="cf_sql_char" ) />
                                                                            <cfset queryObj.addParam( name="ip", value=currentIP, cfsqltype="cf_sql_varchar" ) />
                                                                            <cfset queryObj.addParam( name="description", value="导入 " & rs_term.academic_year & "-" & rs_term.academic_term & "学期《" & rs_course.course_name & "》 成绩, 正考成绩 " & markAdvice.getMark( rs_course.mark_mode, mark, markNote ), cfsqltype="cf_sql_varchar" ) />
                                                                            
                                                                            <cfset queryObj.execute( sql=sql ) />
                                                                            
																		
																			<!--- /* 第二步 变更成绩历史记录 */ --->
                                                                            <cfset sequenceStudentMark.setStudentID(rs_student.stu_id) />
                                                                            <cfset sequenceStudentMark.setCourseID(rs_course.cid) />
                                                                            <cfset sequenceStudentMark.setTermID(rs_term.term_id) />
                                                                            <cfset sequenceStudentMark.setTaskType(taskType) />
                                                                            
                                                                            <cfset markId = sequenceStudentMark.getID() />
                                                                            
                                                                             
                                                                            <cfset sql = "SELECT mark_resit, mark_resit_limit FROM t_student_mark WHERE scm_id = :markId " /> 
                                                                            
                                                                            <cfset queryObj = new Query( datasource=application.dnsMaster ) /> 
                                                                            <cfset queryObj.addParam( name="markId", value=markId, cfsqltype="cf_sql_varchar" ) /> 
                                                                            
                                                                            <cfset rs_markCheck = queryObj.execute( sql=sql ).getResult() /> 
                                                                            
                                                                            
																			<!--- 数据准备完成 开始导入成绩数据 --->
																			
																			<cfif rs_markCheck.recordCount EQ 0>
																					<cfset markInsert++ />
                                                                                    
                                                                                    <cfset sql = "INSERT INTO t_student_mark(
                                                                                                        scm_id, stu_id, term_id, cid, prop_id, retaking, 
                                                                                                        mark_mode, mark_result, mark_note, mark_resit, mark_renote,
                                                                                                        task_type, task_id, tch_id, mark_time,
                                                                                                        mark_resit_time, scc_id, mark_resit_limit, remarks, credit_point 
                                                                                                  )VALUES(
                                                                                                        :markId, :studentId, :termId, :courseId, :propId, :retaking, 
                                                                                                        :markMode, :markResult, :markNote, :markResit, :markResitNote, 
                                                                                                        :taskType, :taskId, :teacherId, :markTime, 
                                                                                                        :markResitTime, :chooseId, :markResitLimit, :remarks, :creditPoint 
                                                                                                  )" />
                                                                                    
                                                                                    <cfset queryObj = new Query( datasource=application.dnsMaster ) />
                                                                                    
                                                                                    <cfset queryObj.addParam( name="markId", value=markId, cfsqltype="cf_sql_varchar" ) />
                                                                                    <cfset queryObj.addParam( name="studentId", value=rs_student.stu_id, cfsqltype="cf_sql_varchar" ) />
                                                                                    <cfset queryObj.addParam( name="termId", value=rs_term.term_id, cfsqltype="cf_sql_char" ) />
                                                                                    <cfset queryObj.addParam( name="courseId", value=rs_course.cid , cfsqltype="cf_sql_varchar" ) />
                                                                                    <cfset queryObj.addParam( name="propId", value=rs_courseProp.prop_id, cfsqltype="cf_sql_char" ) />
                                                                                    <cfset queryObj.addParam( name="retaking", value=retaking, cfsqltype="cf_sql_char" ) />
                                                                                    <cfset queryObj.addParam( name="markMode", value=rs_course.mark_mode, cfsqltype="cf_sql_char" ) />
                                                                                    <cfset queryObj.addParam( name="markResult", value=mark, cfsqltype="cf_sql_decimal", scale=2 ) />
                                                                                    <cfset queryObj.addParam( name="markNote", value=markNote, cfsqltype="cf_sql_char" ) />
                                                                                    <cfset queryObj.addParam( name="markResit", value=-1, cfsqltype="cf_sql_decimal", scale=2 ) />
                                                                                    <cfset queryObj.addParam( name="markResitNote", value=1, cfsqltype="cf_sql_char" ) />
                                                                                    <cfset queryObj.addParam( name="taskType", value=taskType, cfsqltype="cf_sql_char" ) />
                                                                                    <cfset queryObj.addParam( name="taskId", value=rs_term.term_id & "," & rs_course.cid & ",IMP", cfsqltype="cf_sql_varchar" ) />
                                                                                    <cfset queryObj.addParam( name="teacherId", value=tchId, cfsqltype="cf_sql_varchar" ) />
                                                                                    <cfset queryObj.addParam( name="markTime", value=currentTS, cfsqltype="cf_sql_char" ) />
                                                                                    <cfset queryObj.addParam( name="markResitTime", value=currentTS, cfsqltype="cf_sql_char" ) />
                                                                                    <cfset queryObj.addParam( name="chooseId", value=rs_student.stu_id & "," & rs_course.cid & "," & rs_term.term_id & taskType, cfsqltype="cf_sql_varchar" ) />
                                                                                    <cfset queryObj.addParam( name="markResitLimit", value=60, cfsqltype="cf_sql_decimal", scale=2 ) />
                                                                                    <cfset queryObj.addParam( name="remarks", value=rs_teacherEntity.tch_name & " 在 " & datetimeAdvice.getDateTime() & " 导入", cfsqltype="cf_sql_varchar" ) />
                                                                                    <cfset queryObj.addParam( name="creditPoint", value=markAdvice.getMarkCredit(rs_course.mark_mode, rs_course.course_credit, mark, -1, 60), cfsqltype="cf_sql_decimal", scale=4 ) />
                                                                                    
                                                                                    <cfset queryObj.execute( sql=sql ) />
																				<cfelse>
																					<cfset markUpdate++ />
                                                                                
                                                                                	<cfset sql = "UPDATE t_student_mark 
																									SET 
																										prop_id = :propId, 
																										retaking = :retaking, 
																										mark_mode = :markMode, 
																										mark_result = :markResult, 
																										mark_note = :markNote,
																										task_type = :taskType,
																										task_id = :taskId, 
																										mark_time = :markTime,
																										remarks = :remarks,
																										credit_point = :creditPoint 
																									WHERE 
																										scm_id = :markId" />
                                                                                    
                                                                                    <cfset queryObj = new Query( datasource=application.dnsMaster ) />
                                                                                    
                                                                                    <cfset queryObj.addParam( name="markId", value=markId, cfsqltype="cf_sql_varchar" ) />      
                                                                                    <cfset queryObj.addParam( name="propId", value=rs_courseProp.prop_id, cfsqltype="cf_sql_char" ) />                                                                              
                                                                                    <cfset queryObj.addParam( name="retaking", value=retaking, cfsqltype="cf_sql_char" ) />
                                                                                    <cfset queryObj.addParam( name="markMode", value=rs_course.mark_mode, cfsqltype="cf_sql_char" ) />
                                                                                    <cfset queryObj.addParam( name="markResult", value=mark, cfsqltype="cf_sql_decimal", scale=2 ) />
                                                                                    <cfset queryObj.addParam( name="markNote", value=markNote, cfsqltype="cf_sql_char" ) />
                                                                                    <cfset queryObj.addParam( name="taskType", value=taskType, cfsqltype="cf_sql_char" ) />
                                                                                    <cfset queryObj.addParam( name="taskId", value=rs_term.term_id & "," & rs_course.cid & ",IMP", cfsqltype="cf_sql_varchar" ) />                                                                                    
                                                                                    <cfset queryObj.addParam( name="markTime", value=currentTS, cfsqltype="cf_sql_char" ) /> 
                                                                                    <cfset queryObj.addParam( name="remarks", value=rs_teacherEntity.tch_name & " 在 " & datetimeAdvice.getDateTime() & " 导入", cfsqltype="cf_sql_varchar" ) />
                                                                                    <cfset queryObj.addParam( name="creditPoint", value=markAdvice.getMarkCredit(rs_course.mark_mode, rs_course.course_credit, mark, rs_markCheck.mark_resit, rs_markCheck.mark_resit_limit), cfsqltype="cf_sql_decimal", scale=4 ) />
                                                                                    
                                                                                    <cfset queryObj.execute( sql=sql ) />
                                                                             
																			</cfif>
                                                                            
																		<cfelse>
																			<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) 课程: <cfoutput>#rs["课程"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["课程号"][rs.currentRow]#</cfoutput>) 成绩填写不正确</p>
																	</cfif>

																<cfelse>
																	<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) 课程: <cfoutput>#rs["课程"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["课程号"][rs.currentRow]#</cfoutput>) 课程性质填写不正确</p>
															</cfif>
															
															
														<cfelse>
															<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) 课程: <cfoutput>#rs["课程"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["课程号"][rs.currentRow]#</cfoutput>) 课程信息填写不正确, 没有匹配课程</p>
													</cfif>
													
												<cfelse>
													<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) 填写不正确</p>
											</cfif>
											
											
										<cfelse>
											<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) <cfoutput>#rs["课程"][rs.currentRow]#</cfoutput> 学年、学期填写不正确 </p>											
									</cfif>
									
								<cfelse>
									<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) <cfoutput>#rs["课程"][rs.currentRow]#</cfoutput> 学期填写不正确. 学期的有效值为 1 / 2 / 3 或 "春" / "秋" / "暑期" </p>
							</cfif>
						
						</cfloop>

					</cftransaction>
				
				</cfif>

			</cfif>

		</cfif> 
		
		<p>新增成绩数据 [<cfoutput>#markInsert#</cfoutput>] 条, 更新现有成绩数据 [<cfoutput>#markUpdate#</cfoutput>] 条.</p>
				
		<!--- 删除临时文件 --->
		<cfif fileExists(temFile)>
			<cffile action="delete" file="#temFile#" />		
		</cfif>
	
	</cfif>

</body>
</html>